# encoding:utf8

from dbutils.pooled_db import PooledDB
from schema import Schema, And


class database_class():
    def __init__(self, creator, database_config):
        database_info = Schema(
            {
                "host": And(str),  # 数据库IP
                "db": And(str),  # 数据库名
                "user": And(str),  # 数据库用户名
                "passwd": And(str),  # 数据库密码
                "port": And(int)  # 数据库端口
            }
        )
        creator = creator  # 数据库驱动模块，如常见的pymysql,pymssql,cx_Oracle模块。无默认值
        mincached = 0  # 初始化连接池时创建的连接数。默认为0，即初始化时不创建连接。(建议默认0，假如非0的话，在某些数据库不可用时，整个项目会启动不了)
        maxcached = 0  # 池中空闲连接的最大数量。默认为0，即无最大数量限制。(建议默认)
        maxshared = 0  # 池中共享连接的最大数量。默认为0，即每个连接都是专用的，不可共享(不常用，建议默认)
        maxconnections = 0  # 被允许的最大连接数。默认为0，无最大数量限制。(视情况而定)
        blocking = False  # 连接数达到最大时，新连接是否可阻塞。默认False，即达到最大连接数时，再取新连接将会报错。(建议True,达到最大连接数时，新连接阻塞，等待连接数减少再连接)
        maxusage = 0  # 连接的最大使用次数。默认0，即无使用次数限制。(建议默认)
        reset = True  # 当连接返回到池中时，重置连接的方式。默认True，总是执行回滚。
        ping = 1  # 确定何时使用ping()    检查连接。默认1，即当连接被取走，做一次ping操作。0:是从不ping，1:默认，2:当该连接创建游标时ping，4:是执行sql语句时ping，7:总是ping
        try:
            is_valid = database_info.is_valid(database_config)
            print(is_valid)
            if is_valid:
                self.pool_list = PooledDB(creator=__import__(creator), mincached=mincached, maxcached=maxcached,
                                          maxshared=maxshared, maxconnections=maxconnections, blocking=blocking,
                                          maxusage=maxusage, reset=reset, ping=ping, host=database_config['host'],
                                          port=database_config['port'], user=database_config['user'],
                                          password=database_config['passwd'], database=database_config['db'])
            else:
                raise Exception("数据库信息格式异常")
        except Exception as error:
            # self.pool_list.close()
            print(f"数据库连接池创建失败:{error}")

    def get_database_connect(self):
        return self.pool_list.connection()

    def select(self, exec_sql: str, to_dict: bool = False) -> list(dict):
        """
        :params exec_sql: 待执行的select语句
        :params to_dict: 将结果转换为list(dict())格式
        """
        conn = self.get_database_connect()
        cursor = conn.cursor()
        try:
            if to_dict:
                # 获取字段名称
                cursor.execute(exec_sql)
                column_names = [col[0] for col in cursor.description]
                select_result = []
                for row in cursor.fetchall():
                    select_result.append(dict(zip(column_names, row)))
            else:
                cursor.execute(exec_sql)
                select_result = [row for row in cursor.fetchall()]
            return select_result
        except Exception as error:
            print(f'数据库查询错误:{error}')
            return []
        finally:
            cursor.close()
            conn.close()
        return []

    def execsql(self, exec_sql: str):
        """
        :param exec_sql: 执行sql
        :return:
        """
        conn = self.get_database_connect()
        cursor = conn.cursor()
        try:
            result = cursor.execute(exec_sql)
            # 提交sql
            cursor.commit()
            return result
        except Exception as error:
            print(f'sql执行异常:{error}')
        finally:
            cursor.close()
            conn.close()
            return Exception(f"sql执行异常")

    def select_params(self, exec_sql: str, *params, to_dict=False):
        """
        :params exec_sql: 待执行的select语句
        :params to_dict: 将结果转换为list(dict())格式
        """
        conn = self.get_database_connect()
        cursor = conn.cursor()
        try:
            if to_dict:
                # 获取字段名称
                cursor.execute(exec_sql, params)
                column_names = [col[0] for col in cursor.description]
                select_result = []
                for row in cursor.fetchall():
                    select_result.append(dict(zip(column_names, row)))
            else:
                cursor.execute(exec_sql, params)
                select_result = [row for row in cursor.fetchall()]
            return select_result
        except Exception:
            pass
        finally:
            cursor.close()
            conn.close()
            return Exception(f"sql执行异常")


import psycopg2

conn = database_class("psycopg2", database_config={"host": "192.168.247.129", "db": "postgres", "user": "postgres",
                                                   "passwd": "test1234", "port": 5432})
rs = conn.select("select count(1) as zhangsan from public.userinfo_tab;", to_dict=True)
print(rs)
